- Special Edition Using Visual Basic Script -

CHAPTER 21 - Accessing Data

by Ibrahim Malluf

In this chapter

• Using data sources
• Using server side data
• The Internet Database Connector (IDC) provides a simple, direct method for accessing data.
• Using client side data
• Building query pages

One of the most exiting aspects of recent developments in Web servers is the ability to access Open Database Connectivity (ODBC) data sources directly through interactive queries. MicrosoftÆs Internet Information Server (MSIIS), for example, provides the IDC interface that automatically queries the database and returns a result page based on the specifics of an HTX file that describes how the output should be formatted. The process involves three steps: the user submits a request for data to the Web server, the request is processed, and a results page is created and returned to the userÆs browser. Traditionally this has been done through CGI scripts with all processing being done on the server side of things. With VBScript, you can validate the query data and determine what type of query should be submitted and even what the return data should look like.

ODBC data sources cover a very wide range of RDBMS from Syquest and Oracle to Microsoft SQL-Server and even ISAM databases through the Jet DBEngine. Microsoft Internet Information Server directly accesses these databases using a very simple interface. While you can still develop CGI scripts and use them for data access, the rest of this chapter is about the IDC interface provided by MicrosoftÆs Internet Information Server.

Using Data Sources

With the Microsoft Internet Information Server (MSIIS), you can access any ODBC-compliant data source. This includes Access, SQL Server, Oracle, DB2, and many others. This book uses SQL-Server and Access for its database examples. When using the ODBC databases in conjunction with the MSIIS, you have to create the datasource as a DSN entry:

1. Select the ODBC icon from the control panel (NT351).

2. Select the System DSN command button.

3. At this point you should see the dialogs shown in Figure 21.1. Fill in the required information including the database name in the options section.

FIG. 21.1

Add a DSN datasource to the system.

Using Server Side Data

On the server side of things, an array of methods is available for processing data. Depending on your platform, you can use CGI, perl, Java, Visual Basic, and many other tools. Our focus here is on the Microsoft Internet Information Server, so our examples discuss the main tool made available for database operations: the Internet Database Connector.

The Internet Database Connector (IDC) is an interface between the MSIIS Web server and ODBC data sources. If you are looking for a simple, direct method for accessing data, this is an outstanding choice. It requires three files to produce an HTML results page. The IDC file structure contains the components shown in Table 21.1.

Table 21.1 Components of the IDC File

The DataSource, Template, and SQLStatement must be present in every IDC file. The DataSource specifies the ODBC datasource that contains the desired database. When you set this data source up, you must set it up as a SystemDSN.

The Template specifies the name of the .HTX file that contains the formatting information for the recordset returned from the query. The file looks just like an HTML file but contains special formatting instructions that modify the output HTML file with special keywords and values. Here are some of those keywords and values with examples of their use.

<%begindetail%>...<%enddetail%>

These two keywords mark the beginning and end of a merged data row. For each row returned, the details included between them will be repeated (see Listing 21.1).

Listing 21.1 Inserting the Data Returned from the IDC into a Web Page

This code would add the name of every author returned in the recordset into a dropdown box on the resultant HTML form.

<%if...%>...<%Else%>...<%EndIf%>

Conditional keywords are provided so that you can include some branching logic in the creation of your HTML page. This If...Else...EndIf structure should be familiar to most BASIC programmers. The conditional statement is contained within the If statement: <%If Conditional%>. There is no Then statement after it (see Listing 21.2).

Listing 21.2 Using Conditional Statements in a Template File

Listing 21.2 is a variation on the previous example that creates a dropdown box and adds data to it only if there are rows returned in the rowset. The EQ operator and CurrentRecord value used in this example is explained below.

EQ, LT, GT, CONTAINS Operators

Along with the keywords, the IDC provides some operators to use with .HTX pages to further increase the flexibility of your page templates. Their meanings are pretty straightforward (see Table 12.2).

Table 12.2 HTX Operator Desriptions

In the example for the <%If%>...<%Else%>...<%EdnIf%> statement, you saw the EQ operator used. The LT and GT operators work pretty much the same. The CONTAINS operator is a string searching tool that provides a means for you to check for specific values.

CurrentRecord, MaxRecords Built-In Variables

The CurrentRecord intrinsic variable contains the row number being processed. It is incremented every iteration of the <%begindetail%>...<%enddetail%> pair.

The MaxRecords is the value of the MaxRecords field set in the IDC file. If no value was set, it will return 0.

Both of these variables only work within the <%If...%><%Else%><%EndIf%> conditional statement.

IDC Parameters

Your .HTX file can access the parameters originally passed to the .IDX file by the HTML Query page. If one of the criteria parameteers was named %lName% you could access the value of %lName% by using the construct: IDC.lName

HTTP Variables

HTTP variables contain information about the client sending the request. The complete header is contained in the ALL_HTTP variable. In addition there are HTTP variables that contain specific information. All of theses variables must be prefaced with the HTTP_ prefix and must be all uppercase characters. Table 21.3 gives a list of some of them. For a more comprehensive list, see the MSIIS Server documentation.

Table 21.3 Some HTTP Variables

Suppose you had to know what kind of browser client was requesting data, in order to use, or not use, certain features. The IDC provides access to HTTP variables that include that name of the browser. The ALL_HTTP variable contains a header that has the browser type within the string. To check to see whether you were using Explorer 3.0, for example, you might do something like whatÆs shown in Listing 21.3.

Listing 21.3 Other Conditionals Based on the HTTP Variables

To see exactly how the IDC works, try a simple page using Microsoft Internet Information Server. Looking at Figure 21.2, you can see the steps involved in retrieving data. The first HTML page provides the user with a method of specifying criteria for the search. When the user is done specifying criteria, the criteria are submitted to an .IDC file that gets the resultset and submits it to an .HTX file that formats the data into an HTML page and returns it to the browser. This process can be repeated as many times as you wish, creating a drill-down search with each query returned building a new set of search options. For now, weÆll try a simple query page that will give you a working example of how things fit together.

FIG. 21.2

The Internet Database Connector in action, step by step.

Using Client Side Data

Client side data includes search criteria, data entry, and returned rows from a query. Search criteria uses the input from a user to build a query to submit to the data server. What you most commonly see in Web pages are single-entry searches or searches that allow multiple fields with little control over whether itÆs an or or an and type of relationship. They also donÆt allow for sophisticated pattern matching queries.

Data entry is part of client side data. In the past, Web pages have just passed the entered data back to the server as entered by the user without any qualifications. If there was invalid or incomplete data, this could not be checked until the server passed had the data to the program that was responsible for the response to the query. Errors would be detected there, and the appropriate response would then be sent back to the source of the query. Considering that there are situations involving bandwidth, server load, and other factors that could really slow down the interaction between the client and the server, the ability to screen data before sending it becomes paramount. VBScript gives you that ability.

Building Query Pages

The current limitations of the Web server/browser interface even with VBScript dictate limitations on your interface design. Perhaps the most limiting condition when using the IDC is that the way the data is returned must be already defined and formatted in a preexisting .HTX document. The .HTX format allows you considerable formatting options, but the basic document must preexist. This means is that users are going to be limited to the query structures that you provide. Ad-hoc queries simply wonÆt work using the IDC.

As an introduction into building SQL queries for submission, consider this simple scenario. This page will allow the user to select one of three tables from the PUBS sample database in SQL-Server 6. In order for this example to work, you have to have MIIS and SQL-Server 6 up and running. You must also create an ODBC datasource on your server using the ODBC utility in the control panel of NT 3.51 with service pak4 or from NT4.0. The ODBC source must be set up as a DNS datasource. Use the name æPublishersÆ as the name of the datasource and set æPubsÆ as the database name. Using Notepad as your text editor, create the file shown in Listing 21.4 or alternatively, you could retrieve the file from the companion CD. This is an .IDX file that receives the query string from your page and submits it to SQL-Server.

Listing 21.4ùquepub1.idc An .IDX file that requests a rowset from SQL Server

This .IDX file takes two arguments, %lname% and %fname%, as parameters for the query. It passes the query to SQL-Server and then takes the resultset and sends it to the quepub1.htx file that will format the results into a HTML page and give it to the Web server to return it to the client browser. The .HTX file that is being called by this example is in Listing 21.5.

Listing 21.5ùquepub1.htx Template File Used in the Creation of a Return Page that Contains the Requested Data

The key part of this .HTX listing is between the %begindetail%...%enddetail% pair. These two keywords constitute a loop that will iterate through all of the rows returned in the recordset. In this application, the authorÆs first and last names will be added to a dropdown list on the returned Web page.

Finally, the HTML page that calls all of this is presented in Listing 21.6.

Listing 21.6ùdatacc1.htm Initial HTML File that Accepts the Query Parameters and Requests Data from the Server

Listing 21.6 is a simple form that allows the user to enter any characters into the text boxes and then submits that data to the .IDX file. To try this application, type in a character or two with a trailing %. This is SQL ServerÆs wildcard character. In Listing 21.6, you will find a VBScript procedure called FixEntry(). The purpose of this function is to search the string passed to it for any æ%Æ wildcard characters, strip them out, and replace them with an HTML equivalent that can be passed through an HTML argument to a server. The resulting Web page from this document is illustrated in Figure 21.3.

FIG. 21.3

The DacAcc1.htm screen looks like this.

In the example in Figure 21.3, let's run a query that will accept any first name, by using only a % character in the first name field and any last name that starts with Gr by entering Gr% into the last name field. Press the ôclick to send queryö button, and the two parameter fields are sent to the Microsoft Internet Information ServerÆs IDC component. Using the quepub1.idc and quepub1.htx files along with these parameters produces the Web page illustrated in Figure 21.4.

FIG. 21.4

This Web page results from the datacc1.htm to quepub1.idc to quepub1.htx interaction.

The page you see in Figure 21.4 is the result of the quepub1.htx file (refer to Listing 21.5) being fed data from the rowset created through the quepub1.idc file (refer to Listing 21.4). We used an ActiveX ListBox as the container for the data being returned so that users could scroll through the list and easily select the author they seek from the resultset returned.

Listing 21.7ùquepub1.htx Setting up the HTX Code to Load Data into an ActiveX Control

Notice how the section of HTX code shown in Listing 21.7 is converted to a final output source file shown in Listing 21.8

Listing 21.8 View Source of Query: The Resulting Source Code from the quepub1.htx Showing the ieList additem Detail

This is just a simple example of what can be done. With a little imagination, you can create very complex result pages, complete with whatever formatting is needed. Included with this page being returned is not only the ListBox loaded with the query results but also a floating frame. A dummy page is loaded into it initially, so that the frame can accept other documents being loaded into it later. When a user clicks on one of the authors returned in the ListBox, another query is called. The click event of the ListBox is used to send another query to the IDC containing the au_id data from the list box. That click event is shown in Listing 21.9.

Listing 21.9ùquepub1.htx The Click Event of the List Box Calling Another Query and Targeting it into the Floating Frame

All we had to do was create a string that included the file path to the .IDC file, a question mark character, and the expected parameter for this query. Continuing with this example, click the æMarjorie GreenÆ data row, and the resulting parameter is sent to the quepub2.idc file through the MIIS. That file is shown in Listing 21.10, which includes a three-table join.

Listing 21.10ùquepub2.idc The Qupub2.idc File with the Three-Table Join

By now you are probably beginning to see that the .IDC files are fairly straightforward, requiring only a datasource, username, template path, and the query that requests rows from the database. The quepub2.idc, while simple, returns many columns of data. In the .HTX file shown in Listing 21.11, we added a few extras besides just the returning of rows.

Listing 21.11quepub2.htx The Template File with Additional HTTP Variable Information

In the first part of Listing 21.11, we use two variables that are not part of the rowset returned. The <%Query_String%> variable returns the parameters sent to the .IDC file by the client page, in this case quepub1.htx. The <%ALL_HTTP%> variable returns all of the known information about the current transaction, including the server address, the client address, the client browser, and other useful information detailed in Table 21.3. Clicking on any name in the ListBox causes the floating frame to be filled with the data related to the author selected. Figure 21.5 shows the result of clicking a name.

FIG. 21.5

The author information is loaded into the floating frame.

From Here...

The subject of the Miscrosoft Internet Information ServerÆs Internet Database Connector could take a whole book by itself. The intention here was to give you an idea of what can be done using the IDC to build Web pages during runtime on the server side that included VBScript as part of the package. If anything this chapter hopefully gives you some ideas of your own to try out. You can build very sophisticated VBScript based template files using the IDC.